package tech.xujian.easydb;


import tech.xujian.easydb.annotation.AutoId;
import tech.xujian.easydb.annotation.Ignore;
import tech.xujian.easydb.annotation.PrimaryKey;
import tech.xujian.easydb.annotation.Table;
import tech.xujian.easydb.bean.EasyDB;

import java.lang.reflect.Field;
import java.lang.reflect.InvocationTargetException;
import java.lang.reflect.Method;
import java.sql.*;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.*;
import java.util.Date;

/**
 * Created by Reder.Xu on 2016/11/18.
 */
public class DBUtils {

    public static boolean save(Object obj){
        if(obj == null){
            return false;
        }
        String tableName = getClassTable(obj.getClass());

        return save(obj,tableName);
    }

    public static boolean save(Object obj,String table){
        String sql = getSaveSQL(obj,table);
        if(sql == null){
            System.out.println("生成SQL语句错误");
            return false;
        }
        System.out.println("准备执行SQL: " + sql);
        DBHelper dbHelper = new DBHelper(sql);
        boolean ret = false;
        try {
            ret = dbHelper.pst.execute();
            dbHelper.close();//关闭连接
        } catch (SQLException e) {
            e.printStackTrace();
            dbHelper.close();
        }
        return ret;
    }

    private static String getSaveSQL(Object obj,String table){
        if(obj == null){
            System.out.println("存储对象不能为空");
            return null;
        }
        String tableName = null;
        if(table == null){
            if(obj instanceof EasyDB){
                tableName = ((EasyDB)obj).getTableName();
            }else{
                tableName = obj.getClass().getSimpleName();
            }
        }else{
            tableName = table;
        }
        String sql = "insert into " + tableName + " (";
        String names = "";
        String values = "'";
        Field[] filds = obj.getClass().getDeclaredFields();
        try {
            for (Field field : filds) {
                //处理autoId注解，如果是自增长id，则跳过
                AutoId autoId = field.getAnnotation(AutoId.class);
                if(autoId != null){
                    continue;
                }

                String name = field.getName();
                String getMethodName = FieldUtils.getFieldGetMethod(obj.getClass(),field).getName();
                String value = "";
                if(field.getType().getName().equals(Date.class.getName())){
                    Date date = (Date) obj.getClass().getMethod(getMethodName).invoke(obj);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    value = sdf.format(date);
                }else{
                    value = "" + obj.getClass().getMethod(getMethodName).invoke(obj);
                }

                value = value.replaceAll("'","-");

                //处理注解属性名
                tech.xujian.easydb.annotation.Field fieldAnnotation = field.getAnnotation(tech.xujian.easydb.annotation.Field.class);
                if(fieldAnnotation != null){
                    names += fieldAnnotation.name() + ",";
                }else{
                    names += humpToLine(name) + ",";
                }
                values += value + "','";
            }
            names = names.substring(0,names.length() - 1);
            values = values.substring(0,values.length() - 2);
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            return null;
        } catch (InvocationTargetException e) {
            e.printStackTrace();
            return null;
        } catch (NoSuchMethodException e) {
            e.printStackTrace();
            return null;
        }

        sql += names + ") values (" + values + ")";
        return sql;
    }

    //存储列表
    public static int saveList(List<?> objectList){
        if(objectList.size() == 0){
            return -1;
        }

        Table table = objectList.get(0).getClass().getAnnotation(Table.class);
        String tableName = null;
        if(table != null){
            tableName = table.name();
        }

        DBHelper dbHelper = new DBHelper();
        Connection conn = dbHelper.conn;
        try {
            conn.setAutoCommit(false);
            for (Object o : objectList) {
                String sql = getSaveSQL(o,tableName);
                if(sql == null){
                    continue;
                }
                PreparedStatement ps = conn.prepareStatement(sql);
                ps.execute();
            }
            conn.commit();
            dbHelper.close();
            return objectList.size();
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return -1;
    }

    public static <T> List<T> find(Class c, String items[], String values[]){
        String tableName = c.getSimpleName().toLowerCase();
        String sql = "select * from " + tableName;
        sql += getWhereSQL(items,values);
        System.out.println(sql);
        DBHelper dbHelper = new DBHelper(sql);
        List<T> list = query(dbHelper,c);
        return list;
    }

    public static <T> List<T> find(Class c,String sql){
        DBHelper dbHelper = new DBHelper(sql);
        List<T> list = query(dbHelper,c);
        return list;
    }

    public static List<Map<String,Object>> findMap(String sql){
        DBHelper dbHelper = new DBHelper(sql);
        return queryMap(dbHelper);
    }

    public static boolean update(Class c,String[] setItems,String[] setValues,String[] whereItems,String[] whereValues){
        String tableName = getClassTable(c);
        return update(tableName.toLowerCase(),setItems,setValues,whereItems,whereValues);
    }

    public static boolean update(String tableName,String[] setItems,String[] setValues,String[] whereItems,String[] whereValues){
        if(setItems == null || setItems.length == 0 || setValues == null || setValues.length != setItems.length){
            System.err.println("setItems == null ==> " + (setItems == null));
            System.err.println("setItems.length == 0 ==> " + (setItems == null || setItems.length == 0));
            System.err.println("setValues == null ==> " + (setValues == null));
            System.err.println("setValues.length != setItems.length ==> " + (setItems == null || setValues == null || setValues.length != setItems.length));
            System.err.println("DBUtils.update 参数异常");
            return false;
        }
        String sql = "update " + tableName + " set ";
        for(int i = 0; i < setItems.length;i++){
            String key = setItems[i];
            key = humpToLine(key);
            String value = setValues[i];
//            value = value.replaceAll("'", "\\'");
            sql += ("`" + key  + "` = '" + value + "' ,");
        }
        sql = sql.substring(0,sql.length() - 1);
        sql += getWhereSQL(whereItems,whereValues);
        if(sql.endsWith(",")){
            sql = sql.substring(0,sql.length() - 1);
        }
        System.out.println(sql);
        DBHelper dbHelper = new DBHelper(sql);
        return doSQL(dbHelper);
    }

    public static boolean execute(String sql){
        DBHelper dbHelper = new DBHelper(sql);
        return doSQL(dbHelper);
    }

    public static boolean delete(Class c, String items[], String values[]){
        return delete(c.getSimpleName().toLowerCase(),items,values);
    }

    public static boolean delete(String tableName, String items[], String values[]){
        String sql = "delete from " + tableName;
        sql += getWhereSQL(items,values);
        System.out.println(sql);
        DBHelper dbHelper = new DBHelper(sql);
        return doSQL(dbHelper);
    }

    private static boolean doSQL(DBHelper dbHelper){
        try {
            dbHelper.pst.execute();
        } catch (SQLException e) {
            e.printStackTrace();
            dbHelper.close();
            return false;
        }
        dbHelper.close();
        return true;
    }

    private static String getWhereSQL(String items[], String values[]){
        String sql = " ";
        if(items != null && values != null && items.length == values.length && items.length != 0){
            sql += " where ";
            for(int i = 0; i < items.length;i++){
                String key = items[i];
                key = humpToLine(key);
                String value = values[i];
                value = value.replaceAll("'","-");
                sql += key + " = '" + value + "' and ";
            }
            sql = sql.substring(0,sql.length() - " and ".length());
        }
        return sql;
    }

    private static String getWhereSQLOr(String items[], String values[]){
        String sql = " ";
        if(items != null && values != null && items.length == values.length && items.length != 0){
            sql += " where ";
            for(int i = 0; i < items.length;i++){
                String key = items[i];
                key = humpToLine(key);
                String value = values[i];
                value = value.replaceAll("'","-");
                sql += key + " = '" + value + "' or ";
            }
            sql = sql.substring(0,sql.length() - " or ".length());
        }
        return sql;
    }

    private static String toFirstLetterUpperCase(String str) {
        if(str == null || str.length() < 2){
            return str;
        }
        String firstLetter = str.substring(0, 1).toUpperCase();
        return firstLetter + str.substring(1, str.length());
    }

    public static <T> List<T> query(DBHelper dbHelper, Class c){
        List<T> list = new ArrayList<T>();
        try {
            ResultSet ret = dbHelper.pst.executeQuery();
            while (ret.next()) {
                T obj = null;
                try {
                    obj = (T) c.newInstance();
                } catch (InstantiationException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                } catch (IllegalAccessException e1) {
                    // TODO Auto-generated catch block
                    e1.printStackTrace();
                }
                if(obj == null){
                    ret.close();
                    dbHelper.close();
                    return list;
                }
                Field[] fields = c.getDeclaredFields();
                Class nC = c.getSuperclass();
                if(nC != null){
                    Field[] subFields = nC.getDeclaredFields();
                    Field[] nFileds = new Field[fields.length + subFields.length];
                    int i = 0;
                    for (Field field : fields) {
                        nFileds[i] = field;
                        ++i;
                    }
                    for (Field field : subFields) {
                        nFileds[i] = field;
                        ++i;
                    }
                    fields = nFileds;
                }
                for (Field field : fields) {
                    String name = humpToLine(field.getName());
                    Ignore ignore = field.getAnnotation(Ignore.class);
                    if(ignore != null){
                        continue;
                    }
                    String colValue = "" + (ret.getString(name));
                    Method m = FieldUtils.getFieldSetMethod(c, field);
                    if(m == null){
                        continue;
                    }
                    String type = field.getType().getSimpleName();
                    if(type.equalsIgnoreCase("String")){
                        m.invoke(obj, colValue);
                    }else if(type.equalsIgnoreCase("int") || type.equalsIgnoreCase("Integer")){
                        if(colValue.length() == 0 ||colValue.equalsIgnoreCase("null")){
                            colValue = "0";
                        }
                        m.invoke(obj, Integer.parseInt(colValue));
                    }else if(type.equalsIgnoreCase("byte") || type.equalsIgnoreCase("Byte")){

                        if(colValue.length() == 0 ||colValue.equalsIgnoreCase("null")){
                            colValue = "0";
                        }
                        m.invoke(obj,  Byte.parseByte(colValue));
                    }else if(type.equalsIgnoreCase("double") || type.equalsIgnoreCase("Double")){
                        m.invoke(obj,  Double.parseDouble(colValue));
                    }else if(type.equalsIgnoreCase("float") || type.equalsIgnoreCase("Float")){
                        m.invoke(obj, Float.parseFloat(colValue));
                    }else if(type.equalsIgnoreCase("boolean") || type.equalsIgnoreCase("Boolean")){
                        m.invoke(obj, colValue.equalsIgnoreCase("true"));
                    }else if(type.equalsIgnoreCase("long") || type.equalsIgnoreCase("Long")){
                        m.invoke(obj, Long.parseLong(colValue));
                    }else if(type.equalsIgnoreCase("Date")){
                        if(colValue != null && !colValue.equalsIgnoreCase("null")){
                            try {
                                SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                                m.invoke(obj, sdf.parse(colValue));
                            } catch (ParseException e) {
                                e.printStackTrace();
                            }
                        }
                    }else{
                        if(colValue != null && !colValue.equalsIgnoreCase("null")){
                            m.invoke(obj, colValue);
                        }
                    }
                }
                list.add(obj);
            }
            ret.close();
            dbHelper.close();
        } catch (SQLException e) {
            e.printStackTrace();
            dbHelper.close();
        } catch (InvocationTargetException e) {
            e.printStackTrace();
            dbHelper.close();
        } catch (IllegalAccessException e) {
            e.printStackTrace();
            dbHelper.close();
        }
        return list;
    }

    private static List<Map<String,Object>> queryMap(DBHelper dbHelper){
        List<Map<String,Object>> maps = new ArrayList<Map<String,Object>>();
        try {
            ResultSet ret = dbHelper.pst.executeQuery();
            while (ret.next()) {
                Map<String,Object> map = new HashMap();
                ResultSetMetaData m = ret.getMetaData();
                for(int i=1; i <= m.getColumnCount();i++ )
                {
                    map.put(m.getColumnName(i),ret.getString(m.getColumnName(i)));
                }
                maps.add(map);
            }
            ret.close();
            dbHelper.close();
        } catch (SQLException e) {
            e.printStackTrace();
            dbHelper.close();
        }
        return maps;
    }

    public static <T>List<T> findByOr(Class c, String items[], String values[]){
        String tableName = c.getSimpleName().toLowerCase();
        String sql = "select * from " + tableName;
        sql += getWhereSQLOr(items,values);
        System.out.println(sql);
        DBHelper dbHelper = new DBHelper(sql);
        List<T> list = query(dbHelper,c);
        return list;
    }

    public static void insert(List<?> objs){
        try{
            DBHelper dbHelper = new DBHelper();
            Connection conn = dbHelper.conn;
            conn.setAutoCommit(false);
            Statement st = conn.createStatement();
            for (Object obj : objs) {
                String sql = getSaveSQL(obj,null);
                st.addBatch(sql);
            }
            st.executeBatch();
            conn.commit();
            dbHelper.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }

    /**
     * 事务执行sql数组
     * */
    public static void doSQLs(List<String> sqls){
        if(sqls == null || sqls.size() == 0){
            return;
        }
        try{
            DBHelper dbHelper = new DBHelper();
            Connection conn = dbHelper.conn;
            conn.setAutoCommit(false);
            Statement st = conn.createStatement();
            for (String sql : sqls) {
                st.addBatch(sql);
            }
            st.executeBatch();
            conn.commit();
            dbHelper.close();
        }catch (Exception e){
            e.printStackTrace();
        }
    }


    public static String getClassTable(Class c){
        String tableName = c.getSimpleName().toLowerCase();
        Table tableAnnotation = (Table) c.getAnnotation(Table.class);
        if(tableAnnotation != null){
            tableName = tableAnnotation.name();
        }
        return tableName;
    }

    private static String getPrimaryKey(Class c){
        Field[] fields =  c.getDeclaredFields();
        for (Field field : fields) {
            PrimaryKey primaryKey = field.getAnnotation(PrimaryKey.class);
            if(primaryKey != null){
                tech.xujian.easydb.annotation.Field fieldAnnotation = field.getAnnotation(tech.xujian.easydb.annotation.Field.class);
                if(fieldAnnotation != null ){
                    return fieldAnnotation.name();
                }else{
                    return humpToLine(field.getName());
                }
            }
        }
        return null;
    }


    public static <T>T findById(Class c,Object id){
        String primaryKey = getPrimaryKey(c);
        if(primaryKey == null){
            System.err.println("No primaryKey found for class : " + c.getName());
            return null;
        }
        String sql = "SELECT * FROM " + getClassTable(c) + " WHERE " + primaryKey + " = '" + id + "' LIMIT 1";
        System.out.println(sql);
        List<T> list = find(c,sql);
        if(list.size() == 0){
            return null;
        }
        return list.get(0);
    }

    public static <T>T findOne(Class c,String sql){
        List<T> list = find(c,sql);
        if(list.size() == 0){
            return null;
        }
        return list.get(0);
    }

    public static void updateById(Object obj) {
        try{
            String primaryKey = getPrimaryKey(obj.getClass());
            if(primaryKey == null){
                throw new Exception("primaryKey is not found.");
            }
            Field fieldPrimary = obj.getClass().getDeclaredField(primaryKey);
            String getMethodName = FieldUtils.getFieldGetMethod(obj.getClass(),fieldPrimary).getName();
            String primaryKeyValue  = "" + obj.getClass().getMethod(getMethodName).invoke(obj);


            List<String> keys = new ArrayList<String>();
            List<String> values = new ArrayList<String>();

            for (Field field : obj.getClass().getDeclaredFields()) {
                //处理autoId注解，如果是自增长id，则跳过
                AutoId autoId = field.getAnnotation(AutoId.class);
                if(autoId != null){
                    continue;
                }
                PrimaryKey primaryKeyAnnotation = field.getAnnotation(PrimaryKey.class);
                if(primaryKeyAnnotation != null){
                    continue;
                }

                String name = field.getName();
                getMethodName = FieldUtils.getFieldGetMethod(obj.getClass(),field).getName();
                String value = "";
                if(field.getType().getName().equals(Date.class.getName())){
                    Date date = (Date) obj.getClass().getMethod(getMethodName).invoke(obj);
                    SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");
                    value = sdf.format(date);
                }else{
                    if(obj.getClass().getMethod(getMethodName).invoke(obj) == null){
                        value = null;
                    }else{
                        value = "" + obj.getClass().getMethod(getMethodName).invoke(obj);
                    }
                }
                if(value == null){
                    System.out.println(name + " has no value , continue.");
                    continue;
                }

                //处理注解属性名
                tech.xujian.easydb.annotation.Field fieldAnnotation = field.getAnnotation(tech.xujian.easydb.annotation.Field.class);
                if(fieldAnnotation != null){
                    name = fieldAnnotation.name() ;
                }else{
                    name = humpToLine(name);
                }
                keys.add(name);
                values.add(value);
            }

            String[] id = {primaryKey};
            String[] idValues = {primaryKeyValue};
            int sizeKeys = keys.size();
            if(keys.size() == 0){
                System.err.println("set keys is empty");
                return;
            }
            if(keys.size() != values.size()){
                System.err.println("set values is not the same with set items.");
                return;
            }
            DBUtils.update(obj.getClass(), keys.toArray(new String[sizeKeys]),values.toArray(new String[sizeKeys]),id,idValues);
        }catch (Exception e){
            System.err.println(e.getMessage());
            e.printStackTrace();
        }
    }

    /**
     * 计算数量
     * */
    public static int count(String sql){
        List<Map<String,Object>> maps =  findMap(sql);
        for (String key : maps.get(0).keySet()) {
            return Integer.parseInt(maps.get(0).get(key) + "");
        }
        return 0;
    }


    public static int count(String table,Map<String,String> map){
        String sql = "SELECT COUNT(*) FROM `" + table + "` WHERE 1 = 1 ";
        for (String key : map.keySet()) {
            sql += " AND (`" + key + "` = '" + map.get(key) + "' ) ";
        }
        return count(sql);
    }

    public static String humpToLine(String str) {
        if(str == null){
            return null;
        }
        return str.replaceAll("[A-Z]", "_$0").toLowerCase();
    }
}
